/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.1.3                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          crime1.dez                                      */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2011-08-17 19:28                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "SYS_USER"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [SYS_USER] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [IDROLE] INTEGER,
    [USERNAME] VARCHAR(40),
    [PASSWORD] VARCHAR(15),
    [FIRSTNAME] VARCHAR(40),
    [LASTNAME] VARCHAR(40),
    [SEX] INTEGER,
    [BIRTH_DATE] VARCHAR(40),
    [ADDRESS] VARCHAR(40),
    [STATUS] INTEGER,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_SYS_USER] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "ROLE"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [ROLE] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [NAME] VARCHAR(40),
    [Last_updated] DATETIME,
    CONSTRAINT [PK_ROLE] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "ROLE_MENU"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE [ROLE_MENU] (
    [IDMENU] INTEGER NOT NULL,
    [IDROLE] INTEGER NOT NULL,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_ROLE_MENU] PRIMARY KEY ([IDMENU], [IDROLE])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "SYS_MENU"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [SYS_MENU] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [NAME] VARCHAR(40),
    [Last_updated] DATETIME,
    CONSTRAINT [PK_SYS_MENU] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "COMPLAINT_REG"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [COMPLAINT_REG] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [NAME] VARCHAR(40),
    [AGE] INTEGER,
    [SEX] SMALLINT,
    [ADDRESS] VARCHAR(100),
    [PHONE] VARCHAR(40),
    [EMAIL] VARCHAR(40),
    [DETAIL] TEXT,
    [DATE] DATETIME,
    [PLACE] VARCHAR(40),
    [IDUSER] INTEGER,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_COMPLAINT_REG] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "CRIMINAL_REG"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CRIMINAL_REG] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [FIRSTNAME] VARCHAR(40),
    [LASTNAME] VARCHAR(40),
    [BIRTH] DATETIME,
    [ADDRESS] VARCHAR(50),
    [SEX] INTEGER,
    [IMAGE] VARCHAR(40),
    [IDUSER] INTEGER,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_CRIMINAL_REG] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "CRIME_DETAIL"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CRIME_DETAIL] (
    [ID] INTEGER NOT NULL,
    [IDCRIMINAL] INTEGER,
    [IDCOMPLAINT] INTEGER,
    [STATUS] INTEGER,
    [STATUS_CRIME] INTEGER,
    [DATE_TEMP] DATETIME,
    [DATE_END] DATETIME,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_CRIME_DETAIL] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "POSTMOTEM"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE [POSTMOTEM] (
    [ID] INTEGER NOT NULL,
    [IDFIR] INTEGER,
    [NAME] VARCHAR(40),
    [AGE] VARCHAR(40),
    [SEX] INTEGER,
    [IMAGE] VARCHAR(40),
    [ADDRESS] VARCHAR(50),
    [DOCTOR] VARCHAR(40),
    [RESULT] TEXT,
    [PLACE] VARCHAR(40),
    [DATE] DATETIME,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_POSTMOTEM] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "HISTORY"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [HISTORY] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [DATE] DATETIME,
    [DETAIL] TEXT,
    [PLACE] VARCHAR(40),
    [Last_updated] DATETIME,
    CONSTRAINT [PK_HISTORY] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "LIST_CRIME"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [LIST_CRIME] (
    [ID] INTEGER IDENTITY(0,1) NOT NULL,
    [IDCRIME] INTEGER,
    [NAME] VARCHAR(40),
    [IMPRISONMENT] INTEGER,
    [Last_updated] DATETIME,
    CONSTRAINT [PK_LIST_CRIME] PRIMARY KEY ([ID])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "CRAME"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [CRAME] (
    [CRIMEID] INTEGER NOT NULL,
    [LISTCRIMEID] INTEGER NOT NULL,
    CONSTRAINT [PK_CRAME] PRIMARY KEY ([CRIMEID], [LISTCRIMEID])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [SYS_USER] ADD CONSTRAINT [ROLE_SYS_USER] 
    FOREIGN KEY ([IDROLE]) REFERENCES [ROLE] ([ID])
GO

ALTER TABLE [ROLE_MENU] ADD CONSTRAINT [ROLE_ROLE_MENU] 
    FOREIGN KEY ([IDROLE]) REFERENCES [ROLE] ([ID])
GO

ALTER TABLE [ROLE_MENU] ADD CONSTRAINT [SYS_MENU_ROLE_MENU] 
    FOREIGN KEY ([IDMENU]) REFERENCES [SYS_MENU] ([ID])
GO

ALTER TABLE [COMPLAINT_REG] ADD CONSTRAINT [SYS_USER_COMPLAINT_REG] 
    FOREIGN KEY ([IDUSER]) REFERENCES [SYS_USER] ([ID])
GO

ALTER TABLE [CRIMINAL_REG] ADD CONSTRAINT [SYS_USER_CRIMINAL_REG] 
    FOREIGN KEY ([IDUSER]) REFERENCES [SYS_USER] ([ID])
GO

ALTER TABLE [CRIME_DETAIL] ADD CONSTRAINT [CRIMINAL_REG_CRIME_DETAIL] 
    FOREIGN KEY ([IDCRIMINAL]) REFERENCES [CRIMINAL_REG] ([ID])
GO

ALTER TABLE [CRIME_DETAIL] ADD CONSTRAINT [COMPLAINT_REG_CRIME_DETAIL] 
    FOREIGN KEY ([IDCOMPLAINT]) REFERENCES [COMPLAINT_REG] ([ID])
GO

ALTER TABLE [CRIME_DETAIL] ADD CONSTRAINT [HISTORY_CRIME_DETAIL] 
    FOREIGN KEY ([ID]) REFERENCES [HISTORY] ([ID])
GO

ALTER TABLE [POSTMOTEM] ADD CONSTRAINT [HISTORY_POSTMOTEM] 
    FOREIGN KEY ([IDFIR]) REFERENCES [HISTORY] ([ID])
GO

ALTER TABLE [CRAME] ADD CONSTRAINT [CRIME_DETAIL_CRAME] 
    FOREIGN KEY ([CRIMEID]) REFERENCES [CRIME_DETAIL] ([ID])
GO

ALTER TABLE [CRAME] ADD CONSTRAINT [LIST_CRIME_CRAME] 
    FOREIGN KEY ([LISTCRIMEID]) REFERENCES [LIST_CRIME] ([ID])
GO
